package com.example.demozy.mapper;

import com.example.demozy.entity.CommodityCheck;
import com.example.demozy.entity.ConditionSheetCommodity;
import com.example.demozy.entity.ConditionSheetOperationData;
import com.example.demozy.entity.Order;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author 陆智荣
 */
@Mapper
public interface ConditionSheetCommodityMapper {

    /**
     * 获取商品所有信息
     */
    @Select("select * from condition_sheet_commodity")
    List<ConditionSheetCommodity> selectCommodity();

    /**
     * 根据commodity_id获取商品信息
     */
    @Select("select * from condition_sheet_commodity where commodity_id = #{commodityId}")
    ConditionSheetCommodity getConditionSheetCommodityById(String commodityId);

    /**
     * 根据commodity_id获取用户个人简介
     */

    @Select("SELECT user_description\n" +
            "FROM basic_information, condition_sheet_commodity, " +
            "condition_sheet\n" +
            "WHERE condition_sheet_commodity.commodity_id = #{commodityId}\n" +
            "AND condition_sheet.condition_sheet_id = " +
            "condition_sheet_commodity.condition_sheet_id\n" +
            "AND condition_sheet.author_id = basic_information.user_id")
    String getUserDescriptionByCommodityId(String commodityId);

    /**
     * 根据commodity_id获取用户昵称
     */

    @Select("SELECT nickname\n" +
            "FROM basic_information, condition_sheet_commodity, " +
            "condition_sheet\n" +
            "WHERE condition_sheet_commodity.commodity_id = #{commodityId}\n" +
            "AND condition_sheet.condition_sheet_id = " +
            "condition_sheet_commodity.condition_sheet_id\n" +
            "AND condition_sheet.author_id = basic_information.user_id")
    String getUserNicknameByCommodityId(String commodityId);

    /**
     * 根据commodity_id获取商品所有运营数据
     */

    @Select("SELECT *\n" + "FROM condition_sheet_operation_data\n" +
            "WHERE commodity_id = #{commodityId}")
    ConditionSheetOperationData selectConditionSheetOperationData(String commodityId);

    /**
     * 更新商品的PV运营数据
     */

    @Insert("UPDATE condition_sheet_operation_data\n" +
            "SET condition_sheet_pv = condition_sheet_pv + 1\n" + "WHERE\n" +
            "\t(\n" + "\t\tSELECT\n" + "\t\t\tcommodity_id\n" +
            "\t\tFROM\n" + "\t\t\tcondition_sheet_commodity\n" + "\t\tWHERE\n" +
            "\t\t\tcommodity_id = #{commodityId}\n" + "\t) = commodity_id")
    void insertConditionSheetOperationData(String commodityId);

    /**
     * 根据commodity_id获取用户ID
     */

    @Select("SELECT author_id\n" +
            "FROM condition_sheet_commodity, condition_sheet\n" +
            "WHERE condition_sheet_commodity.commodity_id = #{commodityId}\n" +
            "AND condition_sheet.condition_sheet_id = " +
            "condition_sheet_commodity.condition_sheet_id")
    String getUserIdByCommodityId(String commodityId);

    /**
     *根据commodity_id进行商品购买
     */
    @Update("UPDATE user_wallet\n" + "SET user_balance =\n" + "IF (\n" +
            "\t(\n" + "\t\t(\n" + "\t\t\tSELECT\n" +
            "\t\t\t\tuw.user_balance\n" + "\t\t\tFROM\n" + "\t\t\t\t(\n" +
            "\t\t\t\t\tSELECT\n" + "\t\t\t\t\t\tuser_balance\n" +
            "\t\t\t\t\tFROM\n" + "\t\t\t\t\t\tuser_wallet\n" +
            "\t\t\t\t\tWHERE\n" + "\t\t\t\t\t\tuser_id = #{userId}\n" +
            "\t\t\t\t) uw\n" + "\t\t) - (\n" + "\t\t\tSELECT\n" +
            "\t\t\t\tcommodity_price\n" + "\t\t\tFROM\n" +
            "\t\t\t\tcondition_sheet_commodity\n" + "\t\t\tWHERE\n" +
            "\t\t\t\tcommodity_id = #{commodityId}\n" + "\t\t) >= 0\n" + "\t),\n" + "\t(\n" +
            "\t\t(\n" + "\t\t\tSELECT\n" + "\t\t\t\tuw.user_balance\n" +
            "\t\t\tFROM\n" + "\t\t\t\t(\n" + "\t\t\t\t\tSELECT\n" +
            "\t\t\t\t\t\tuser_balance\n" + "\t\t\t\t\tFROM\n" +
            "\t\t\t\t\t\tuser_wallet\n" + "\t\t\t\t\tWHERE\n" +
            "\t\t\t\t\t\tuser_id = #{userId}\n" + "\t\t\t\t) uw\n" + "\t\t) - (\n" +
            "\t\t\tSELECT\n" + "\t\t\t\tcommodity_price\n" + "\t\t\tFROM\n" +
            "\t\t\t\tcondition_sheet_commodity\n" + "\t\t\tWHERE\n" +
            "\t\t\t\tcommodity_id = #{commodityId}\n" + "\t\t)\n" + "\t),\n" + "\t0\n" +
            ")\n" + "WHERE\n" + "\tuser_wallet.user_id = #{userId}")
    void purchaseCommodityByCommodityId (String commodityId, String userId);

    /**
     *搜索用户余额
     */
    @Select("SELECT user_balance\n" + "FROM user_wallet\n" + "WHERE user_id = #{userId}")
    double selectUserBalance (String userId);



    /**
     *插入购买数据至订单表
     */
    @Insert("INSERT INTO `order`\n" +
            "(`order`.user_id, `order`.order_id, `order`.commodity_id, `order`.goods_price, `order`.pay_way, `order`.pay_time)\n" +
            "SELECT #{userId}, 0, #{commodityId}, #{goodsPrice}, 1, NOW()\n" + "FROM DUAL WHERE NOT EXISTS\n" +
            "(SELECT `order`.commodity_id FROM `order` WHERE `order`.commodity_id = #{commodityId} AND `order`.user_id = #{userId})")
    void insertOrder (String userId, String commodityId, double goodsPrice);

    /**
     *搜索订单表的商品id
     */
    @Select("SELECT commodity_id, user_id\n" + "FROM `order`\n" +
            "WHERE commodity_id = #{commodityId} AND user_id = #{userId}")
    List<String> selectOrderCommodityId (String userId, String commodityId);

    /**
     * 获取所有状态为未审核的条件单
     */
    @Select("SELECT *\n" + "FROM commodity_check")
    List<CommodityCheck> getConditionSheetsWhereOnStateIsOne ();

    /**
     * 审核通过更新条件单状态
     */
    @Update("UPDATE condition_sheet\n" + "SET on_state = 1\n" +
            "WHERE condition_sheet_id = #{conditionSheetId}")
    void updateConditionSheetOnStateToTwo(String conditionSheetId);

    /**
     * 审核驳回更新条件单状态
     */
    @Update("UPDATE condition_sheet\n" + "SET on_state = 0\n" +
            "WHERE condition_sheet_id = #{conditionSheetId}")
    void updateConditionSheetOnStateToZero(String conditionSheetId);

    /**
     * 审核通过删除条件单记录
     */
    @Delete("DELETE FROM commodity_check\n" + "WHERE condition_sheet_id = #{conditionSheetId}")
    void deleteConditionSheetRecord(String conditionSheetId);

    /**
     * 审核通过过审条件单插入到商品表
     */
    @Insert("INSERT INTO condition_sheet_commodity\n" +
            "(commodity_id, condition_sheet_id, commodity_name, " +
            "commodity_description, commodity_price, commodity_upload_time)\n" +
            "VALUES \n" +
            "((SELECT cm.commodity_id FROM (SELECT max(cast(commodity_id as " +
            "SIGNED))+1 AS commodity_id from condition_sheet_commodity)AS cm)" +
            " , \n" +
            "#{conditionSheetId}, #{commodityName}, #{commodityDescription}, #{commodityPrice}, NOW())")
    void insertConditionSheetIntoCommodity(String conditionSheetId, String commodityName, String commodityDescription, double commodityPrice);

    /**
     * 获取订单表中所有订单
     */
    @Select("SELECT *\n" + "FROM `order`\n" + "WHERE user_id = #{userId}")
    List<Order> selectAllOrders(String userId);

    /**
     * 条件单购买成功新增开通数
     */
    @Update("UPDATE condition_sheet_operation_data\n" +
            "SET condition_sheet_registration = condition_sheet_registration " +
            "+ 1\n" +
            "WHERE commodity_id = #{commodityId}")
    void updateconditionSheetRegistration(String commodityId);

    /**
     * 审核通过过审条件单插入运营数据表
     */
    @Insert("INSERT INTO condition_sheet_operation_data\n" +
            "(commodity_id, condition_sheet_uv, condition_sheet_pv, " +
            "condition_sheet_registration, condition_sheet_statistic_date, " +
            "update_time)\n" +
            "VALUES\n" +
            "((SELECT op.commodity_id FROM (SELECT max(LAST_INSERT_ID" +
            "(commodity_id)) AS commodity_id from condition_sheet_commodity)" +
            "AS op) , \n" +
            "0, 0, 0, NOW(), NOW())")
    void insertNewConditionSheetOperationData();

    /**
     * 插入条件单商品审核数据
     *
     * @author 张展鹏
     */
    @Insert("insert into `commodity_check` " +
            "values(0,#{conditionSheetId},#{commodityName},#{onState},#{commodityDescription}," +
            "#{commodityPrice},now(),#{authorId})")
    int insertConditionSheetCommodityCheck(String conditionSheetId, String commodityName, int onState,
            String commodityDescription, double commodityPrice, String authorId);


}
